begin work;

CREATE TABLE ZIPF(
   POSTK char(10) NOT NULL,
   POSTI char(1) NOT NULL,
   WOONP varchar(30),
   DATCR timestamp,
   DATCH timestamp,
   SWLEU char(1) NOT NULL,
   PRIMARY KEY (POSTK,POSTI));

CREATE TABLE FAMI
(
   FAMID int NOT NULL,
   NAAM1 varchar(40) NOT NULL,
   NAAM2 varchar(40),
   NAAMF varchar(50),
   STRAA varchar(40),
   HUISN varchar(10),
   POSTK varchar(10),
   POSTI char(1),
   WOONP varchar(30),
   DATCR timestamp,
   DATCH timestamp,
   SORNA varchar(40),
   PRIMARY KEY (FAMID));

CREATE TABLE FAML(
   	LIDID int,
   	FAMID int NOT NULL,
   	NAAM varchar(30) NOT NULL,
   	VNAAM varchar(30),
   	NAAMF varchar(40),
   	GEBDT date,
   	DATCR timestamp,
   	DATCH timestamp,
   	VRBLI int,
   	VRBDT date,
   	SWSPE char(1) NOT NULL,
   	SWTRA char(1) NOT NULL,
   	SWBES char(1) NOT NULL,
   	SWRCL char(1) NOT NULL,
   	CLUBN varchar(30),
   	SWOUD char(1) NOT NULL,
   	SWREF char(1) NOT NULL,
   	SWSYM char(1) NOT NULL,
   	SEXMV char(1) NOT NULL,
   	POSIT smallint,
   	CAPAC smallint,
   	SORNA varchar(30),
   	DATIN date,
   	TRATY char(1),
   	SCHID integer,
   	BNAAM varchar(30),
   	SWVRL char(1) NOT NULL,
   	BANKR varchar(12),
   	BABIC varchar(8),
   	BIBAN varchar(16),
   	LOGIN varchar(50),
   	PASSW varchar(15),
   	RNAAM varchar(30),
   	IDCARD varchar(15),
   	PLAAT varchar(10),
   	KMRCL int,
   	SWAFB char(1),
   	TAALK varchar(1),
   	PRIMARY KEY (LidId));
   
   	
CREATE TABLE FLCO(
   LIDID integer NOT NULL,
   VOLGN smallint NOT NULL,
   COMTY char(1) NOT NULL,
   SWDEF char(1) NOT NULL,
   BESCH varchar(50),
   DATCR timestamp,
   DATCH timestamp,
   NUMME varchar(50) NOT NULL,
   PRIMARY KEY (LIDID,VOLGN));
   
CREATE TABLE WEDS(
   	WEDID int NOT NULL,
   	SEIID int,
   	DATUM date,
   	UUR time,
   	VERZA varchar(50),
   	VERUU time,
   	DATCR timestamp,
   	DATCH timestamp,
   	PLATY char(1),
   	TEGEN varchar(30),
   	PLOID int NOT NULL,
   	WEDTY char(1) NOT NULL,
   	BRBNR varchar(10),
   	UITSL varchar(20),
   	SWARC char(1),
   	WSURL varchar(100),
   	URLVI varchar(100),
   	URLFO varchar(100),
   	PRIMARY KEY (WedId));
 
CREATE TABLE WEDE(
	WEDID int NOT NULL,
   	LIDID int NOT NULL,
   	DATCR timestamp,
   	DATCH timestamp,
   	POSIT smallint,
   	SWTRA char(1) NOT NULL,
   	SWSPE char(1) NOT NULL,
   	SWBEG char(1) NOT NULL,
   	SWAFW char(1),
   	SWBEV char(1),
   	DATVE date,
   	BERID int,
	PRIMARY KEY (WedId,LidId));
	
CREATE TABLE SEIZ
(
   SEIID int NOT NULL,
   TITLE varchar(50),
   DATVA date,
   DATTO date,
   DATCR timestamp,
   DATCH timestamp,
   SWAKT char(1),
   VELTO numeric(36,4),
   VELKV numeric(36,4),
   VELPR numeric(36,4),
   VELAD numeric(36,4),
   VELWA numeric(36,4),
   VERKV numeric(36,4),
   VERPR numeric(36,4),
   VERAD numeric(36,4),
   VERWA numeric(36,4),
   PRIMARY KEY (SEIID));

CREATE TABLE CATE(
   CATID int NOT NULL,
   TITLE varchar(50) NOT NULL,
   DATCR timestamp,
   DATCH timestamp,
   SEXMV char(1) NOT NULL,
   SW1PL char(1) NOT NULL,
   SWLIC char(1) NOT NULL,
   VERIA int,
   SWLIM char(1),
   PRIMARY KEY (CATID));   
   
CREATE TABLE PLOE
(
   PLOID int PRIMARY KEY NOT NULL,
   TITLE varchar(50),
   CATID int NOT NULL,
   DATCR timestamp,
   DATCH timestamp,
   SEXMV char(1) NOT NULL,
   MNEMO varchar(3),
   SWWEB char(1),
   SHNAM varchar(15),
   SWACT char(1),
   VERIA int NOT NULL,
   PRIMARY KEY (PLOID));

CREATE TABLE PLSE(
   PLOID int NOT NULL,
   SEIID int NOT NULL,
   SWACT char(1) NOT NULL,
   DATCR timestamp,
   DATCH timestamp,
   PRIMARY KEY (PLOID,SEIID));

   
--FOREIGN KEYS
ALTER TABLE FAMI ADD CONSTRAINT FK_FAMI_ZIPF FOREIGN KEY(POSTK,POSTI)
REFERENCES ZIPF(POSTK,POSTI);

ALTER TABLE WEDE ADD CONSTRAINT FK_WEDE_FAML FOREIGN KEY (LIDID)
REFERENCES FAML(LIDID);

ALTER TABLE WEDE ADD CONSTRAINT FK_WEDE_WEDS FOREIGN KEY (WEDID)
REFERENCES WEDS(WEDID);

ALTER TABLE WEDS ADD CONSTRAINT FK_WEDS_SEIZ FOREIGN KEY (SEIID)
REFERENCES SEIZ(SEIID);

ALTER TABLE PLOE ADD CONSTRAINT FK_PLOE_CATE FOREIGN KEY (CATID)
REFERENCES CATE(CATID);

ALTER TABLE PLSE ADD CONSTRAINT FK_PLSE_SEIZ FOREIGN KEY (SEIID)
REFERENCES SEIZ(SEIID);

ALTER TABLE PLSE ADD CONSTRAINT FK_PLSE_PLOE FOREIGN KEY (PLOID)
REFERENCES PLOE(PLOID);

ALTER TABLE WEDS ADD CONSTRAINT FK_WEDS_PLSE FOREIGN KEY(PLOID,SEIID)
REFERENCES PLSE(PLOID,SEIID);

ALTER TABLE FLCO ADD CONSTRAINT FK_FLCO_FAML FOREIGN KEY (LIDID)
REFERENCES FAML(LIDID);

commit work;